ALTER  function fn_yield_assembly()

returns  @fn_result Table(ProcessStepID int, LotNumber varchar(20), ProductName varchar(30), DateIn datetime, DateOut datetime, 
			Al_Dice_Discrp_In int,  AL_Disc_Discrp_Out int, AL_Disc_Discrp_Yield float, AL_Die_Inspect_In int,
			AL_Die_Inspect_Out int, AL_Die_Inspect_Yield float, AL_Pre_Mold_In int, AL_Pre_Mold_Out int, 
			AL_Pre_Mold_Yield float, Al_Ic_Finishing_In int, Al_Ic_Finishing_Out int, Al_Ic_Finishing_Yield int,  
			Al_Inv_Yield float, Al_Act_Yield float, AL_Asy_Yield float, Al_Qty_In int, Al_Qty_Out int)
as

begin
/*variable to store the values
*/
	declare @qty_in int, @qty_out int,  @lot_id int
	declare @disc_in int, @disc_out int, @ins_in int, @ins_out int, @mold_in int, @mold_out int, @fin_in int, @fin_out int
	declare @disc_yield float, @ins_yield float, @mold_yield float, @fin_yield float, @inv_yield float, @act_yield float, @asy_yield float
	declare @lot_no varchar(20), @prod_name varchar(20)
	declare @date_in datetime, @date_out datetime
	declare @step_id int

	
	declare @results Table(ProcessStepID int, LotNumber varchar(20), ProductName varchar(30), DateIn datetime, DateOut datetime, 
			Al_Dice_Discrp_In int,  AL_Disc_Discrp_Out int, AL_Disc_Discrp_Yield float, AL_Die_Inspect_In int,
			AL_Die_Inspect_Out int, AL_Die_Inspect_Yield float, AL_Pre_Mold_In int, AL_Pre_Mold_Out int, 
			AL_Pre_Mold_Yield float, Al_Ic_Finishing_In int, Al_Ic_Finishing_Out int, Al_Ic_Finishing_Yield int,  
			Al_Inv_Yield float, Al_Act_Yield float, AL_Asy_Yield float, Al_Qty_In int, Al_Qty_Out int)
	
	--select * from Assembly_lot_history
	declare assemblyLotCursor cursor for
		select Lot_ID, AL_Date_In, AL_Date_Out, AL_Dice_Discrp_In, AL_Dice_Discrp_Out, AL_Dice_Discrp_Yield, 
			AL_Die_Inspect_In, AL_Die_Inspect_Out, AL_Die_Inspect_Yield, AL_Pre_Mold_In, AL_Pre_Mold_Out, 
			AL_Pre_Mold_Yield, AL_Ic_Finishing_In, AL_Ic_Finishing_Out, AL_Ic_Finishing_Yield, AL_Inv_Yield, 
			AL_Act_Yield, AL_Asy_Yield, AL_Qty_In, AL_Qty_Out from Assembly_Lot_History where AL_Current_Status = 'close'
		open assemblyLotCursor
		fetch next from assemblyLotCursor into @lot_id, @date_in, @date_out, @disc_in, @disc_out, @ins_in, @ins_out, @mold_in,
			 @mold_out, @fin_in, @fin_out,@disc_yield, @ins_yield, @mold_yield, @fin_yield, @inv_yield, @act_yield, 
			@asy_yield,  @qty_in, @qty_out 
		while @@fetch_status = 0
		begin
			select @lot_no=Lot_Number, @step_id = ProcessStep_ID from lot_table where lot_ID = @lot_id
			select @prod_name =part_Number from product where processStep_ID = @step_id
			insert into @results values(@step_id, @lot_no, @prod_name,@date_in, @date_out, @disc_in, @disc_out, @ins_in, @ins_out, @mold_in,
			 @mold_out, @fin_in, @fin_out,@disc_yield, @ins_yield, @mold_yield, @fin_yield, @inv_yield, @act_yield, 
			@asy_yield,  @qty_in, @qty_out )
			set @date_in =null
			set @date_out=null
			set  @qty_in=null
			set  @qty_out=null

			set @disc_in=null
			set @disc_out=null
			set @ins_in=null
			set  @ins_out=null
			set  @mold_in=null
			set @mold_out=null
			set  @fin_in=null
			set  @fin_out=null
			set @disc_yield=null
			set @ins_yield=null
			set @mold_yield=null	
			set @fin_yield=null
			set  @inv_yield=null
			set  @act_yield=null
			set @asy_yield=null 
			
		fetch next from assemblyLotCursor into @lot_id, @date_in, @date_out, @disc_in, @disc_out, @ins_in, @ins_out, @mold_in,
			 @mold_out, @fin_in, @fin_out,@disc_yield, @ins_yield, @mold_yield, @fin_yield, @inv_yield, @act_yield, 
			@asy_yield,  @qty_in, @qty_out 
		end
		close assemblyLotCursor
		deallocate assemblyLotCursor
	
		
		insert @fn_result select * from @results 
return
end




